package com.wyd.common.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * mysql sql加工工具
 */
@Slf4j
public class MySqlWhere {
    public static MySqlWhere getInstance() {
        return new MySqlWhere();
    }

    private String where = "";
    private String select = "SELECT ";
    private String from = " FROM ";
    private String groupBy = "";
    private String orderBy = "";
    /**
     * 返回的最大行数
     */
    private Integer count;
    /**
     * 返回的第一行的偏移量
     */
    private Integer offset;
    private final HashMap<String, Object> map = new HashMap<>();

    /**
     * 处理column字段参数问题
     * column可能含有“`”
     * column可能含有“.”
     * 例子：`name`、u.`name`
     */
    private String getColumnParamName(String column) {
        return column.replace(".", "_").replace("`", "");
    }

    public MySqlWhere select(String select) {
        this.select += select;
        return this;
    }

    /**
     * 新增参数
     */
    public MySqlWhere param(String param, Object value) {
        map.put(param, value);
        return this;
    }

    /**
     * 新增一批参数
     */
    public MySqlWhere param(Map<String, Object> map) {
        this.map.putAll(map);
        return this;
    }

    public MySqlWhere from(String from) {
        this.from += from;
        return this;
    }

    public MySqlWhere groupBy(String groupBy) {
        this.groupBy = " GROUP BY " + groupBy;
        return this;
    }

    public MySqlWhere orderBy(String orderBy) {
        this.orderBy = " ORDER BY " + orderBy;
        return this;
    }

    public MySqlWhere isNull(String column) {
        where += " AND " + getColumnParamName(column) + " IS NULL";

        return this;
    }

    public MySqlWhere isNotNull(String column) {
        where += " AND " + getColumnParamName(column) + " IS NOT NULL";

        return this;
    }

    public MySqlWhere eq(String column, Object value) {
        if (value != null) {
            String columnParamName = getColumnParamName(column);

            where += " AND " + column + " = :" + columnParamName;
            map.put(columnParamName, value);
        }

        return this;
    }

    /**
     * 字符串不为null且不为空字符串才拼接
     *
     * @param column 字段
     * @param value  字符串
     * @return
     */
    public MySqlWhere eqNotBlank(String column, String value) {
        if (StringUtils.isNotBlank(value)) {
            String columnParamName = getColumnParamName(column);

            where += " AND " + column + " = :" + columnParamName;
            map.put(columnParamName, value);
        }

        return this;
    }

    /**
     * 不等于
     */
    public MySqlWhere notEq(String column, Object value) {
        if (value != null) {
            String columnParamName = getColumnParamName(column);

            where += " AND " + column + " != :" + columnParamName;
            map.put(columnParamName, value);
        }

        return this;
    }

    public <T> MySqlWhere in(String column, Collection<T> value) {
        if (value != null && !CollectionUtils.isEmpty(value)) {
            String columnParamName = getColumnParamName(column);

            where += " AND " + column + " IN :" + columnParamName;
            map.put(columnParamName, value);
        }

        return this;
    }

    public MySqlWhere like(String column, String value) {
        if (StringUtils.isNotBlank(value)) {
            final String columnParamName = getColumnParamName(column);

            where += " AND " + column + " LIKE :" + columnParamName;
            map.put(columnParamName, value);
        }

        return this;
    }

    public MySqlWhere between(String column, Date start, Date end) {
        String columnParamName = getColumnParamName(column);

        where += " AND " + column + " BETWEEN :" + columnParamName + "_start AND :" + columnParamName + "_end";
        map.put(columnParamName + "_start", start);
        map.put(columnParamName + "_end", end);

        return this;
    }

    public MySqlWhere greaterThanOrEqualTo(String column, Object value) {
        if (value != null) {
            final String columnParamName = getColumnParamName(column) + "_greaterThanOrEqualTo";
            where += " AND " + column + " >= :" + columnParamName;
            map.put(columnParamName, value);
        }

        return this;
    }

    public MySqlWhere greaterThan(String column, Object value) {
        if (value != null) {
            final String columnParamName = getColumnParamName(column) + "_greaterThan";
            where += " AND " + column + " > :" + columnParamName;
            map.put(columnParamName, value);
        }

        return this;
    }

    public MySqlWhere lessThan(String column, Object value) {
        if (value != null) {
            final String columnParamName = getColumnParamName(column) + "_lessThan";
            where += " AND " + column + " < :" + columnParamName;
            map.put(columnParamName, value);
        }

        return this;
    }

    public MySqlWhere lessThanOrEqualTo(String column, Object value) {
        if (value != null) {
            final String columnParamName = getColumnParamName(column) + "_lessThan";
            where += " AND " + column + " <= :" + columnParamName;
            map.put(columnParamName, value);
        }

        return this;
    }

    public MySqlWhere count(int count) {
        this.count = count;
        return this;
    }

    /**
     * 设置偏移量必须设置条数
     *
     * @param offset
     * @param count
     * @return
     */
    public MySqlWhere offset(int offset, int count) {
        this.offset = offset;
        this.count = count;
        return this;
    }

    /**
     * 追加有参数where sql
     */
    public MySqlWhere plusWhere(String sql, String columnParamName, Object value) {
        if (value != null) {
            where += addAnd(sql);

            map.put(columnParamName, value);
        }

        return this;
    }

    /**
     * 追加无参数where sql
     *
     * @return
     */
    public MySqlWhere plusWhere(String sql) {
        where += addAnd(sql);

        return this;
    }

    public String getWhere() {
        return addWhere(where);
    }

    public Query getNativeQuery(String sql, EntityManager entityManager) {
        log.debug(sql);

        final Query nativeQuery = entityManager.createNativeQuery(sql);
        map.forEach(nativeQuery::setParameter);

        return nativeQuery;
    }

    /**
     * 拼接sql
     */
    public String getSql() {
        String sql = select + from + " " + getWhere();

        if (StringUtils.isNotBlank(groupBy)) {
            sql += groupBy;
        }

        if (StringUtils.isNotBlank(orderBy)) {
            sql += orderBy;
        }

        if (offset != null) {
            sql += " LIMIT " + offset + ", " + count;
        } else if (count != null) {
            sql += " LIMIT " + count;
        }

        return sql;
    }

    public Query getNativeQuery(EntityManager entityManager) {
        return getNativeQuery(getSql(), entityManager);
    }

    public Query getNativeQuery(String sql, EntityManager entityManager, Class resultClass) {
        final Query nativeQuery = entityManager.createNativeQuery(sql, resultClass);
        map.forEach(nativeQuery::setParameter);

        return nativeQuery;
    }

    /**
     * 返回参数map
     */
    public Map<String, Object> getParamMap() {
        return map;
    }

    public static final String AND = "AND";
    public static final String WHERE = "WHERE";

    /**
     * 去除开头的“and”开头加“where”
     * 如果为空字符串则不做处理
     * 如果开头是“where”则不做处理
     */
    public static String addWhere(String whereSql) {
        String result = whereSql.trim();

        if (result.isEmpty()) {
            return result;
        }

        if (StringUtils.startsWithIgnoreCase(result, WHERE)) {

            return result;
        }

        if (StringUtils.startsWithIgnoreCase(result, AND)) {

            result = result.substring(AND.length()).trim();
        }

        return WHERE + " " + result;
    }

    /**
     * 前面添加and 如果有则忽略
     */
    public String addAnd(String sql) {
        String result = sql.trim();

        if (result.isEmpty()) {
            return result;
        }

        if (StringUtils.startsWithIgnoreCase(result, AND)) {
            return " " + result;
        }

        return " AND " + result;
    }
}
